SQL
library(Lahman)
library(sqldf)## Loading required package: gsubfn## Loading required package: proto## Loading required package: RSQLiteLevel one header
leavel 2 header
level 3 header
You can type normally to write paragraphs that will appear in your documents
- Item one in a list
- Item 2
- Item 3
- Item 4
This is how to make a table with multiple columns
| column 1 | cloumn 2 |
|---|---|
| Lions | NFC |
| Patriots | AFC |
| Buccaneers | AFC |
| Eagles | NFC |
Baseball Facts First Fact, I absolutely hate baseball.
These are examples of ques run that gather different data and also show how we can organize how the information will be displayed, example the 4th one.
query<-"SELECT playerID,yearID,teamID,HR FROM Batting
WHERE teamID='PHI' and yearID>=1970 and yearID<=1979 and HR>= 30"
sqldf(query)## playerID yearID teamID HR
## 1 johnsde01 1971 PHI 34
## 2 montawi01 1971 PHI 30
## 3 schmimi01 1974 PHI 36
## 4 luzingr01 1975 PHI 34
## 5 schmimi01 1975 PHI 38
## 6 schmimi01 1976 PHI 38
## 7 luzingr01 1977 PHI 39
## 8 schmimi01 1977 PHI 38
## 9 luzingr01 1978 PHI 35
## 10 schmimi01 1979 PHI 45query<-"SELECT playerID,yearID,teamID,HR FROM Batting
WHERE teamID='NYA' and HR> 40"
sqldf(query)## playerID yearID teamID HR
## 1 ruthba01 1920 NYA 54
## 2 ruthba01 1921 NYA 59
## 3 ruthba01 1923 NYA 41
## 4 ruthba01 1924 NYA 46
## 5 ruthba01 1926 NYA 47
## 6 gehrilo01 1927 NYA 47
## 7 ruthba01 1927 NYA 60
## 8 ruthba01 1928 NYA 54
## 9 ruthba01 1929 NYA 46
## 10 gehrilo01 1930 NYA 41
## 11 ruthba01 1930 NYA 49
## 12 gehrilo01 1931 NYA 46
## 13 ruthba01 1931 NYA 46
## 14 ruthba01 1932 NYA 41
## 15 gehrilo01 1934 NYA 49
## 16 gehrilo01 1936 NYA 49
## 17 dimagjo01 1937 NYA 46
## 18 mantlmi01 1956 NYA 52
## 19 mantlmi01 1958 NYA 42
## 20 mantlmi01 1961 NYA 54
## 21 marisro01 1961 NYA 61
## 22 jacksre01 1980 NYA 41
## 23 martiti02 1997 NYA 44
## 24 giambja01 2002 NYA 41
## 25 giambja01 2003 NYA 41
## 26 rodrial01 2005 NYA 48
## 27 rodrial01 2007 NYA 54
## 28 grandcu01 2011 NYA 41
## 29 grandcu01 2012 NYA 43query<-"SELECT playerID,yearID,teamID,HR,SO FROM Batting
WHERE HR> 40 and SO<=60"
sqldf(query)## playerID yearID teamID HR SO
## 1 hornsro01 1922 SLN 42 50
## 2 willicy01 1923 PHI 41 57
## 3 ottme01 1929 NY1 42 38
## 4 ruthba01 1929 NYA 46 60
## 5 gehrilo01 1931 NYA 46 56
## 6 ruthba01 1931 NYA 46 51
## 7 gehrilo01 1934 NYA 49 31
## 8 gehrilo01 1936 NYA 49 46
## 9 troskha01 1936 CLE 42 58
## 10 dimagjo01 1937 NYA 46 37
## 11 mizejo01 1940 SLN 43 49
## 12 mizejo01 1947 NY1 51 42
## 13 willite01 1949 BOS 43 48
## 14 kinerra01 1951 PIT 42 57
## 15 camparo01 1953 BRO 41 58
## 16 rosenal01 1953 CLE 43 48
## 17 kluszte01 1954 CIN 49 35
## 18 mayswi01 1954 NY1 41 57
## 19 kluszte01 1955 CIN 47 40
## 20 mayswi01 1955 NY1 51 60
## 21 aaronha01 1957 ML1 44 58
## 22 sievero01 1957 WS1 42 55
## 23 aaronha01 1969 ATL 44 47
## 24 aaronha01 1971 ATL 47 58
## 25 thomafr04 1993 CHA 41 54
## 26 bondsba01 2002 SFN 46 47
## 27 bondsba01 2003 SFN 45 58
## 28 bondsba01 2004 SFN 45 41
## 29 pujolal01 2004 SLN 46 52
## 30 pujolal01 2006 SLN 49 50query<-"SELECT playerID,teamID,yearID,HR FROM Batting
WHERE HR>50
ORDER BY HR DESC"
sqldf(query)## playerID teamID yearID HR
## 1 bondsba01 SFN 2001 73
## 2 mcgwima01 SLN 1998 70
## 3 sosasa01 CHN 1998 66
## 4 mcgwima01 SLN 1999 65
## 5 sosasa01 CHN 2001 64
## 6 sosasa01 CHN 1999 63
## 7 marisro01 NYA 1961 61
## 8 ruthba01 NYA 1927 60
## 9 ruthba01 NYA 1921 59
## 10 foxxji01 PHA 1932 58
## 11 greenha01 DET 1938 58
## 12 howarry01 PHI 2006 58
## 13 gonzalu01 ARI 2001 57
## 14 rodrial01 TEX 2002 57
## 15 wilsoha01 CHN 1930 56
## 16 griffke02 SEA 1997 56
## 17 griffke02 SEA 1998 56
## 18 ruthba01 NYA 1920 54
## 19 ruthba01 NYA 1928 54
## 20 kinerra01 PIT 1949 54
## 21 mantlmi01 NYA 1961 54
## 22 ortizda01 BOS 2006 54
## 23 rodrial01 NYA 2007 54
## 24 bautijo02 TOR 2010 54
## 25 davisch02 BAL 2013 53
## 26 mantlmi01 NYA 1956 52
## 27 mayswi01 SFN 1965 52
## 28 fostege01 CIN 1977 52
## 29 mcgwima01 OAK 1996 52
## 30 rodrial01 TEX 2001 52
## 31 thomeji01 CLE 2002 52
## 32 kinerra01 PIT 1947 51
## 33 mizejo01 NY1 1947 51
## 34 mayswi01 NY1 1955 51
## 35 fieldce01 DET 1990 51
## 36 jonesan01 ATL 2005 51